package com.easybuilder.common.utils;

import com.easybuilder.common.base.entity.BaseEntity;
import com.easybuilder.common.base.entity.DynamicEntity;
import com.easybuilder.common.base.entity.SearchEntity;
import com.easybuilder.common.config.datasource.DataSourcePool;
import com.easybuilder.common.enums.RelationType;
import org.springframework.boot.autoconfigure.condition.ConditionalOnNotWebApplication;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class DBUtil {
    // java数据类型与数据库类型的关系map
    private static final Map<Integer, String> DATA_TYPE_MAP = new HashMap<>();
    // java数据类型与依赖包map
    private static final Map<String, String> JAVA_PACKAGE_MAP = new HashMap<>();
    // 同步代码块初始化操作
    static {
        // 初始化配置
        init();
    }

    //事务是否结束，依据此标识，选择是否释放资源
    private static final ThreadLocal<Boolean> transcationalOver = new ThreadLocal<>();

    //设置开始事务标识
    public static void setTranscationalStart() {
        transcationalOver.set(false);
    }

    //设置结束事务标识
    public static void setTranscationalOver() {
        transcationalOver.set(true);
    }

    // 创建java实体类
    public static void /**/createJavaEntity(String tableName, String classPath) {
        Connection conn = DataSourcePool.getConnection();
        try {
            Statement statement = conn.createStatement();
            // 查询空数据，通过次方式获取表的字段，主要是方便获取对应的java数据类型
            ResultSet resultSet = statement.executeQuery("select * from " + tableName + " where 1 = 2");
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取字段详细描述信息
            ResultSet resultSet2 = statement.executeQuery("show full columns from " + tableName);
            Map<String, Map<String, String>> columnsInfo = new HashMap<>();
            while (resultSet2.next()) {
                String field = resultSet2.getString("Field");
                String comment = resultSet2.getString("Comment");
                String key = resultSet2.getString("Key");
                Map<String, String> map = new HashMap<>();
                map.put("comment", comment);
                map.put("key", key);
                columnsInfo.put(field, map);
            }
            // 字段名
            List<Map<String, String>> fields = new ArrayList<>();
            // 获取需要导入的包
            List<String> packages = new ArrayList<>();
            List<String> mustImportPackages = Arrays.asList("javax.persistence.Entity", "javax.persistence.Id", "javax.persistence.Table","javax.persistence.GeneratedValue","lombok.Data");
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                // 数据库字段名
                String columnName = metaData.getColumnName(i);
                //  java字段名
                String fieldName = StringUtil.column2Field(columnName);
                // java字段数据类型（全路径名称）
                String className = metaData.getColumnClassName(i);
                // java字段数据类型（短名称）
                String fieldType = className.substring(className.lastIndexOf(".")+1);
                // 添加属性
                Map<String, String> map = new HashMap<>();
                map.put("fieldName",  fieldName);
                map.put("fieldType", fieldType);
                map.put("key", columnsInfo.get(columnName).get("key"));
                map.put("comment", columnsInfo.get(columnName).get("comment"));
                fields.add(map);
                // 导入包
                if (StringUtil.isNotEmpty(className) && !packages.contains(className)) {
                    packages.add(className);
                }
            }
            packages.addAll(mustImportPackages);
            // 生成java实体类
            FileUtil.createJavaEntity(classPath, tableName, fields, packages);
            // 生成dao
            classPath = classPath.replace("entity", "dao");
            FileUtil.createJavaDao(classPath, tableName);
            // 生成service
            classPath = classPath.replace("dao", "service");
            FileUtil.createJavaService(classPath, tableName);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    //操作类型枚举
   public enum OperateType{
        SELECT,
        INSERT,
        UPDATE,
        DELETE
    }

    // 自定义sql，列表查询
    public static List<DynamicEntity> selectList(String sql) {
        ArrayList<DynamicEntity> list = new ArrayList<>();
        if (!checkSqlInjection(sql)) {
            System.out.println("sql提示：不安全的sql语句！");
            return list;
        }
        // 判断是否为查询表字段的语句
        boolean showColumnSql = isShowColumnSql(sql);
        Connection conn = DataSourcePool.getConnection();
        try {
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();

            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                DynamicEntity entity = new DynamicEntity();
                for (int i = 1; i <= columnCount; i++) {
                    int columnType = metaData.getColumnType(i);
                    String columnName = metaData.getColumnName(i);
                    // 如果是查询表字段信息的语句，需要对metdata的column作转换（COLUMN_NAME -> Field）
                    if (showColumnSql) {
                        switch (columnName) {
                            case "COLUMN_NAME": columnName = "Field"; break;
                            case "COLUMN_TYPE": columnName = "Type"; break;
                            case "IS_NULLABLE": columnName = "Null"; break;
                            case "COLUMN_KEY": columnName = "Key"; break;
                            case "COLUMN_DEFAULT": columnName = "Default"; break;
                        }
                    }
                    Object value = null;
                    switch (columnType) {
                        case 12 : value = resultSet.getString(columnName); break;
                        case 4 : value = resultSet.getInt(columnName); break;
                        case 6 : value = resultSet.getDouble(columnName); break;
                        case 93 : value = resultSet.getTimestamp(columnName); break;
                        default: value = resultSet.getString(columnName);
                    }
                    entity.put(StringUtil.column2Field(metaData.getColumnLabel(i)), value);
                }
                list.add(entity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println("sql: " + sql);
        System.out.println("total: " + list.size());
        return list;
    }//endregion

    //统计查询
    public static int countBySql(String sql) {
        Connection conn = DataSourcePool.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            if (!checkSqlInjection(sql)) {
                System.out.println("sql提示：不安全的sql语句！");
                return 0;
            }
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int total = resultSet.getInt(1);
                return total;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(statement, resultSet);
        }
        return 0;
    }

    //insert
    public static long insert(BaseEntity entity) {
       return executeUpdate(buildInsertSql(entity), OperateType.INSERT);
    }

    //update
    public static long update(BaseEntity entity) {

        return executeUpdate(buildUpdateSql(entity), OperateType.UPDATE);
    }

    //delete
    public static long delete(BaseEntity entity) {
        return executeUpdate(buildDeleteSql(entity),OperateType.DELETE);
    }

    // 构建物理删除语句，logic delete
    private static String buildDeleteSql(BaseEntity baseEntity) {
        String tableName = baseEntity.getTableName();
        if (StringUtil.isEmpty(tableName)) {
            throw new RuntimeException("构建delete语句失败，tableName不能为空！");
        }
        DynamicEntity entity = baseEntity.getEntity();
        String ids = entity.getString("ids");
        if (entity == null || StringUtil.isEmpty(ids)) {
            throw new RuntimeException("构建delete语句失败，ids不能为空！");
        }
        StringBuffer sql = new StringBuffer("delete from ").append(tableName).append(" where id in (");
        String[] idsArr = ids.split(",");
        for (String id : idsArr) {
            sql.append("'").append(id).append("',");
        }
        sql.deleteCharAt(sql.lastIndexOf(",")).append(")");
        return sql.toString();
    }

    //executeUpdate（jdbc执行insert、update方法）
    private static long executeUpdate(String sql, OperateType type) {
        System.out.println("执行SQL: " + sql);
        if (!checkSqlInjection(sql)) {
            System.out.println("sql提示：不安全的sql语句！");
            return 0;
        }
        Connection conn = DataSourcePool.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            if (type == OperateType.INSERT) {
                PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                int insert = pstmt.executeUpdate();
                System.out.println("insert: " + insert);
                ResultSet rs = pstmt.getGeneratedKeys();
                while (rs.next()) {
                    return rs.getLong(1);
                }
            }
            if (type == OperateType.UPDATE || type == OperateType.DELETE) {
                int update = statement.executeUpdate(sql);
                System.out.println(type.name() + ": " + update);
                return update;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(statement, resultSet);
        }
        return 0;
    }

    // 写操作（主要针对表结构的操作）
    public static boolean executeUpdate(String sql) {
        System.out.println("执行sql: " + sql);
        boolean success = true;
        Connection conn = DataSourcePool.getConnection();

        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 关闭自动提交事务
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            statement.executeUpdate(sql);
            // 手动提交事务
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            success = false;
            // 异常后事务回滚
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            release(statement, resultSet);
        }
        return success;
    }

    //delete(jdbc)
    private static int delete(String sql) {
        Connection conn = DataSourcePool.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            // statement
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(statement, resultSet);
        }
        return 0;
    }

    //构建insert语句
    private static String buildInsertSql(BaseEntity entity) {
        String tableName = entity.getTableName();
        if (StringUtil.isEmpty(tableName)) {
            throw new RuntimeException("构建insert语句失败，tableName不能为空！");
        }
        if (entity.getEntity() == null || entity.getEntity().size() == 0) {
            throw new RuntimeException("构建insert语句失败，插入字段不能为空！");
        }
        StringBuffer sb = new StringBuffer("insert into ").append(entity.getTableName()).append(" (");
        Set<String> keys = entity.getEntity().keySet();
        Object [] keyArr = keys.toArray();
        Object[] values = new Object[keys.size()];
        for (int i = 0; i < keys.size(); i++) {
            values[i] = entity.getEntity().get(keyArr[i]);
            sb.append(keyArr[i]).append(",");
        }
        sb.deleteCharAt(sb.lastIndexOf(",")).append(")").append("values(");
        for (Object value : values) {
            sb.append("'").append(value).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(",")).append(")");

        return sb.toString();
    }

    //构建update语句
    private static String buildUpdateSql(BaseEntity entity) {
        String tableName = entity.getTableName();
        if (StringUtil.isEmpty(tableName)) {
            throw new RuntimeException("构建update语句失败，tableName不能为空！");
        }
        StringBuffer sb = new StringBuffer("update ").append(entity.getTableName()).append(" set ");
        Set<String> keys = entity.getEntity().keySet();
        keys.forEach(key -> {
            if (!"id".equals(key)) {
                Object value = entity.getEntity().get(key);
                value = value == null ? "" : value;
                sb.append(StringUtil.field2Column(key)).append(" = ").append("'").append(value).append("',");
            }
        });
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(entity.getEntity().get("id"));
        return sb.toString();
    }

    //解析条件语句
    public static String resolveCriteria(List<SearchEntity.Criteria> list) {
        StringBuffer sql = new StringBuffer(" where 1 = 1 ");
        if (list != null && list.size() > 0) {
            list.forEach(item -> {
                String fieldName = item.getFieldName();
                RelationType relationType = item.getRelationType();
                RelationType relationType2 = item.getRelationType2();
                Object value = item.getValue();
                Object value2 = item.getValue2();
                if (StringUtil.isNotEmpty(fieldName)) {
                    //单条件
                    if ((relationType != null && relationType2 == null) && (value != null && value2 == null)) {
                        sql.append(" and ").append(StringUtil.field2Column(fieldName)).append(" ").append(relationType.getValue());
                        sql.replace(sql.lastIndexOf("$"), sql.lastIndexOf("}") + 1, value.toString());
                    }
                    //多条件
                    if ((relationType != null && relationType2 != null) && (value != null && value2 != null)) {
                        sql.append(" and ").append(StringUtil.field2Column(fieldName)).append(" ").append(relationType.getValue());
                        sql.replace(sql.lastIndexOf("$"), sql.lastIndexOf("}") + 1, value.toString());
                        sql.append(" and ").append(StringUtil.field2Column(fieldName)).append(" ").append(relationType2.getValue());
                        sql.replace(sql.lastIndexOf("$"), sql.lastIndexOf("}") + 1, value2.toString());
                    }
                }
            });
        }
        return sql.toString();
    }

    //防止sql注入，过滤掉特殊字符（-、#）
    private static boolean checkSqlInjection(String sql) {
        String[] unsafeChar = {"#", "-"};
        if (StringUtil.isNotEmpty(sql)) {
            for (String s : unsafeChar) {
                if (sql.indexOf(s) > 0) {
                    return false;
                }
            }
        }
        return true;
    }

    //释放资源
    public static void release(Statement statement, ResultSet resultSet) {
        Boolean isOver = transcationalOver.get();
        //如果未启用事务注解，或者事务结束，释放资源
        if (isOver == null || isOver) {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                DataSourcePool.returnConnection();
                transcationalOver.remove();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    // 初始化配置
    private static void init() {
        // 数据库数据类型
        Integer[] dataTypes = new Integer[] {-5, -1, 1, 3, 4, 7, 8, 12, 91, 93};
        // java数据类型
        String[] fieldTypes = new String[] {"Long", "String", "Character", "BigDecimal", "Integer", "Flot", "Double", "String", "Date", "Date"};
        for (int i = 0; i < dataTypes.length; i++) {
            DATA_TYPE_MAP.put(dataTypes[i], fieldTypes[i]);
        }
        // 需要引入包的数据类型
        String[] importDataType = {"Date", "BigDecimal", ""};
        // 对应引入包
        String[] packages = {"java.util.Date", "java.math.BigDecimal", ""};
        for (int i = 0; i < importDataType.length; i++) {
            JAVA_PACKAGE_MAP.put(importDataType[i], packages[i]);
        }
    }

    // 根据数据库数据类型，获取java数据类型
    public static String getJavaType(Integer columnType) {
        return DATA_TYPE_MAP.get(columnType);
    }

    // 根据java数据类型，获取需要引入的包名
    public static String getImportPackage(String fieldType) {
        return JAVA_PACKAGE_MAP.get(fieldType);
    }

    // 判断是否为查询列信息的sql语句
    private static boolean isShowColumnSql(String sql) {
        boolean result = false;
        if (sql.replaceAll(" ", "").toUpperCase().contains("SHOWCOLUMNS")) {
            return true;
        }
        return result;
    }
}
